import xlrd
import pymysql


# 处理excel依赖xlrd模块 pip install xlrd

# 读取excel文件
excel_data = xlrd.open_workbook("t_sys_car_area.xlsx")
# 获取第一个sheet页
sheet = excel_data.sheet_by_index(0)
conn = pymysql.connect(user='root',password='root',database='bladex_boot6',charset="utf8")
cursor = conn.cursor()
# 总行数
rows = sheet.nrows
count = 0
for q in range(0,rows):
    chepai = sheet.cell(q,1).value
    chengshi = sheet.cell(q,2).value
    shengfen = sheet.cell(q,3).value
    shengfenbianma = sheet.cell(q,4).value
    sql = "INSERT INTO t_sys_car_area(id, code, city, province, p_code) VALUES (NULL, '{}','{}', '{}', '{}')".format(chepai,chengshi,shengfen,shengfenbianma)
    try:
        cursor.execute(sql)
        count += 1
    except pymysql.err.IntegrityError:
        pass
cursor.close()
conn.commit()
conn.close()
print('ok')
columns = str(sheet.ncols)
print('导入了{}列{}条数据到mysql！'.format(columns,count))

